/*
	THIS PROC LOADS ALL THE CANCELLED LINEITEMS
	sp_sales_cancellation_report '1'
*/
ALTER       			proc sp_sales_cancellation_report 
 
 @order_type int 

as

declare @ccol_line_item_no int,@ccol_product_id int, @ccol_qty int, @ccol_unit_cost float, @ccol_amount float, @ccol_requested_date datetime 
declare @ccol_schedule_date datetime, @ccol_timestamp datetime, @p_part_number varchar(50), @so_sales_order_id int, @so_sales_order_no varchar(50), @so_customer_id int
declare @so_customer_po_number varchar(50), @so_sales_person varchar(50), @so_rep_office varchar(50),@so_enduser varchar(65), @so_booking_date datetime, @so_is_sample int
declare @c_cname varchar(65), @c_cregion varchar(50), @c_csales_person varchar(50) 
declare @so_Ck_Dt_DT datetime , @so_Ck_Yr_I int,@so_Ck_Mt_I int,@so_Ck_Qt_I int,@so_Ck_Qt_End_I int,@act_year int, @EndCustomer varchar(65)

declare @results table (CCOL_Line_Item_No int, Product_ID int, CCOL_Qty int, CCOL_Unit_Cost float, 
			CCOL_Amount float, CCOL_Requested_Date datetime,CCOL_Schedule_Date datetime, Timestamp datetime, 
			Part_Number varchar(50), Sales_Order_ID int, Sales_Order_NO varchar(50), Customer_ID int,SO_Customer_PO_Number varchar(50),
			SO_Sales_Person varchar(50), SO_EndUser varchar(65), SO_Booking_Date datetime,SO_IS_Sample int,
			C_Name varchar(65), C_Region varchar(50), SO_Rep_Office varchar(50),C_Sales_Person varchar(50),
			Cancel_Year int ,Cancel_Month int, Cancel_Quarter int, Quarter_End int, Act_Year int, EndCustomer varchar(65) ) 
 


declare cancelLineItemCursor cursor for
	
	SELECT  CCOL.CCOL_Line_Item_No, CCOL.Product_ID, CCOL.CCOL_Qty, CCOL.CCOL_Unit_Cost, CCOL.CCOL_Amount, CCOL.CCOL_Requested_Date, 
			CCOL.CCOL_Schedule_Date, CCOL.[Timestamp], P.Part_Number, SO.SalesOrder_ID, SO.SalesOrder_No, SO.Customer_ID, 
			SO.SO_Customer_PO_Number, SO.SO_Sales_Person, 
			CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0  ) 
				THEN C.C_Name --SO CUSTOMER NAME
				ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
				END AS EndUser,
			SO.SO_Booking_Date, SO.SO_Is_Sample, C.C_Name, C.C_Region, SO.Regional_Rep, C.C_Sales_Person,
			CASE WHEN ( SO.SO_ENDCUSTOMER_ID IS NULL OR SO.SO_ENDCUSTOMER_ID <= 0  ) 
				THEN (CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID <= 0 ) 
					THEN C.C_Name --SO CUSTOMER NAME
					ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
					END) 
				ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
				END AS EndCustomer
			FROM Customer_Cancel_Order_LineItem CCOL 
				INNER JOIN Sales_Order SO ON CCOL.SalesOrder_ID = SO.SalesOrder_ID 
				INNER JOIN Product P ON CCOL.Product_ID = P.Product_ID 
				INNER JOIN Customer C ON SO.Customer_ID = C.Customer_ID 

WHERE     (SO.SO_Is_Sample = @order_type)

	open cancelLineItemCursor
	fetch next from cancelLineItemCursor into @ccol_line_item_no ,@ccol_product_id , @ccol_qty , @ccol_unit_cost ,
						  @ccol_amount , @ccol_requested_date , @ccol_schedule_date , 
						  @ccol_timestamp ,@p_part_number , @so_sales_order_id , 
						  @so_sales_order_no , @so_customer_id ,@so_customer_po_number,
						  @so_sales_person,@so_enduser, @so_booking_date, @so_is_sample,@c_cname ,
						  @c_cregion , @so_rep_office ,@c_csales_person, @EndCustomer 
	while @@fetch_status = 0
	begin
		
		set @so_Ck_Dt_DT = null 
		select @so_Ck_Dt_DT = CCOL.Timestamp from Customer_Cancel_Order_LineItem CCOL , Sales_Order SO where CCOL.SALESORDER_ID = SO.SALESORDER_ID AND CCOL.CCOL_Line_Item_No =  @ccol_line_item_no and SO.SalesOrder_ID = @so_sales_order_id  and SO.SO_Is_Sample = @order_type

		set @so_Ck_Yr_I = DATEPART(yyyy, @so_Ck_Dt_DT)
		set @so_CK_Mt_I = DATEPART(mm, @so_Ck_Dt_DT)
		set @act_year = @so_Ck_Yr_I 
		
		if (@so_CK_Mt_I  >=1 and @so_CK_Mt_I <=3)
		begin
			set @so_Ck_Qt_I = 1
			set @so_Ck_Qt_End_I =3
		end
		
		else if (@so_CK_Mt_I >=4 and @so_CK_Mt_I <=6)
		begin
			set @so_Ck_Qt_I = 2
			set @so_Ck_Qt_End_I =6
		end
		
		else if (@so_CK_Mt_I  >=7 and @so_CK_Mt_I <=9)
		begin
			set @so_Ck_Qt_I = 3
			set @so_Ck_Qt_End_I =9
		end						
		
		else if (@so_CK_Mt_I >=10 and @so_CK_Mt_I <=12)
		begin
			set @so_Ck_Qt_I = 4
			set @so_Ck_Qt_End_I =12
			
		end
		
		insert into @results values(@ccol_line_item_no ,@ccol_product_id , @ccol_qty , @ccol_unit_cost ,
						  @ccol_amount , @ccol_requested_date , @ccol_schedule_date , 
						  @ccol_timestamp ,@p_part_number , @so_sales_order_id , 
						  @so_sales_order_no , @so_customer_id ,@so_customer_po_number,
						  @so_sales_person,@so_enduser, @so_booking_date, @so_is_sample,@c_cname ,
						  @c_cregion , @so_rep_office ,@c_csales_person ,
						  @so_Ck_Yr_I ,@so_CK_Mt_I ,@so_Ck_Qt_I ,@so_Ck_Qt_End_I ,@act_year, @EndCustomer )
		
		set @ccol_qty = null 
		set @ccol_unit_cost= null
		set @ccol_amount = null
		set @ccol_requested_date = null
		set @ccol_schedule_date = null
		set @ccol_timestamp = null 
		set @so_sales_order_no = null 
		set @so_customer_po_number = null
		set @so_sales_person = null 
		set @so_enduser = null
		set @so_is_sample = null
		set @c_cregion = null
		set @so_rep_office = null
		set @c_csales_person = null 
		set @EndCustomer = null

		
			
		fetch next from cancelLineItemCursor into @ccol_line_item_no ,@ccol_product_id , @ccol_qty , @ccol_unit_cost ,
						  @ccol_amount , @ccol_requested_date , @ccol_schedule_date , 
						  @ccol_timestamp ,@p_part_number , @so_sales_order_id , 
						  @so_sales_order_no , @so_customer_id ,@so_customer_po_number,
						  @so_sales_person,@so_enduser, @so_booking_date, @so_is_sample,@c_cname ,
						  @c_cregion , @so_rep_office ,@c_csales_person, @EndCustomer
	end
	close cancelLineItemCursor
	deallocate cancelLineItemCursor

	select * from @results

